package com.msr.analysis;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.msr.bean.Navi;
import com.msr.bean.NaviGuide;
import com.msr.bean.NaviPack;
import com.msr.common.DbSource;

public class Analysis {

	public Navi navi() {
		Navi naviBean = new Navi();
		try {
			DbSource d = new DbSource();
			Connection conn = d.getConn();
			PreparedStatement pstmt;
			ResultSet rs;
			/*
			 * 铺货
			 */
			String sqlnavi = "select id,region,sum(cl_hz) as `cl_hz` ,count(*) as `count` from datatable group by region order by region;";
			List<NaviPack> packlist = new ArrayList<NaviPack>();
			pstmt = conn.prepareStatement(sqlnavi);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				NaviPack pack = new NaviPack();
				pack.setRegion(rs.getString("region"));
				pack.setCl_hz(rs.getDouble("cl_hz"));
				pack.setCount(rs.getInt("count"));
				packlist.add(pack);
			}
			rs.close();
			pstmt.close();
			naviBean.setPacklist(packlist);
			/*
			 * 导购
			 */
			String sqlguide = "select id,region,sum(cl_ik) as `cl_ik` ,count(*) as `count` from datatable group by region order by region;";
			List<NaviGuide> guidelist = new ArrayList<NaviGuide>();
			pstmt = conn.prepareStatement(sqlguide);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				NaviGuide guide = new NaviGuide();
				guide.setRegion(rs.getString("region"));
				guide.setCl_ik(rs.getDouble("cl_ik"));
				guide.setCount(rs.getInt("count"));
				guidelist.add(guide);
			}
			rs.close();
			pstmt.close();
			naviBean.setGuidelist(guidelist);

			/*
			 * 主货架
			 */

			/*
			 * 店内活动
			 */
			// String sqlguide =
			// "select id,region,sum(cl_ik) as `cl_ik` ,count(*) as `count` from datatable group by region order by region;";
			// List<NaviGuide> guidelist = new ArrayList<NaviGuide>();
			// pstmt = conn.prepareStatement(sqlguide);
			// rs = pstmt.executeQuery();
			// while (rs.next()) {
			// NaviGuide guide = new NaviGuide();
			// guide.setRegion(rs.getString("region"));
			// guide.setCl_ik(rs.getDouble("cl_ik"));
			// guide.setCount(rs.getInt("count"));
			// guidelist.add(guide);
			// }
			// rs.close();
			// pstmt.close();
			// naviBean.setGuidelist(guidelist);

			conn.close();

		} catch (Exception e) {
			e.printStackTrace();

		}

		return naviBean;
	}

	// 铺货
//	public Pack pack() {
//		Pack pack = new Pack();
//		return pack;
//	}

}
